/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.gov.ana.facade;

import br.gov.ana.entities.Usina;
import java.math.BigDecimal;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

/**
 *
 * @author lucas.nunes
 */
@Stateless
public class UsinaFacade extends AbstractFacade<Usina> {

    @PersistenceContext(unitName = "ResolucaoConjunta032010PU")
    private EntityManager em;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public UsinaFacade(EntityManager em) {
        this.em = em;
    }

    public UsinaFacade() {
        super(Usina.class);
    }

    public Usina findByUsiId(Usina usiId) {
        try {
            Query q = em.createNamedQuery("Usina.findByUsiId").setParameter("usiId", usiId.getUsiId());
            return (Usina) q.getSingleResult();
        } catch (Exception e) {
            return null;
        }
    }

    public List<Usina> findAllUsinaByUsiSituacao(String ORDER, BigDecimal s1, BigDecimal s2) {
        try {
            Query q = em.createQuery("SELECT u FROM Usina u "
                    + "WHERE u.usiUssId.ussId IN (:s1,:s2) ORDER BY :order ASC");
            q.setParameter("s1", s1);
            q.setParameter("s2", s2);
            q.setParameter("order", ORDER);

            return (List<Usina>) q.getResultList();
        } catch (Exception e) {
            return null;
        }
    }

    public List<Usina> findAllAtivos(String ORDER) {
        try {
            // Query q = em.createQuery("SELECT u FROM Usina u WHERE (u.usiSituacao not like :usiSituacao AND u.usiSituacao not like :usiSituacao2) OR u.usiSituacao is null ORDER BY :order asc");
            Query q = em.createQuery("SELECT u FROM Usina u "
                    + "WHERE u.usiUssId.ussId not in (:s1,:s2) OR u.usiUssId is null "
                    + "ORDER BY :order ASC");
            q.setParameter("s1", new BigDecimal ("4")); 
            q.setParameter("s2", new BigDecimal ("5"));
            q.setParameter("order", ORDER);

            return (List<Usina>) q.getResultList();
        } catch (Exception e) {
            return null;
        }
    }

    public List<Usina> findAllUsinaSemTecnico(String ORDER) {
        try {
            //String select = "select distinct u from Usina u where u.usiId not in (select tu.tusUsiId.usiId from TecnicoUsina tu)";
            //String select = "select distinct u from Usina u LEFT JOIN u.tecnicoUsinaList tu where tu.tusTecId is null and (u.usiSituacao is null or u.usiSituacao like :s1 or u.usiSituacao like :s2 or u.usiSituacao like :s3) ORDER BY u.usiNm";
            String select = "select distinct u from Usina u "
                    + "LEFT JOIN u.tecnicoUsinaList tu "
                    + "where tu.tusTecId is null and "
                    + "(u.usiUssId is null or u.usiUssId.ussId in (:s1, :s2, :s3)) "
                    + "ORDER BY :order ASC";

            Query q = em.createQuery(select);
            q.setParameter("s1", new BigDecimal ("1"));
            q.setParameter("s2", new BigDecimal ("2"));
            q.setParameter("s3", new BigDecimal ("3"));          
            q.setParameter("order", ORDER);       

            return (List<Usina>) q.getResultList();
        } catch (Exception e) {
            return null;
        }
    }
    
    public String countUsinasPorTipo(BigDecimal tpuId){
         try {            

            Query q = em.createQuery("select COUNT(u) from Usina u WHERE u.usiTpuId.tpuId = :tpuId AND (u.usiUssId is null OR u.usiUssId.ussId not in (4,5)) ");
            q.setParameter("tpuId", tpuId);                             

            return q.getSingleResult().toString();
        } catch (Exception e) {
            return null;
        }
         //354
    }
}